--===========================================================================================================
-- Data insert script for AuthorizationGroup, Role and RoleMapping tables.
--===========================================================================================================

DECLARE @user nvarchar(50) = SYSTEM_USER
DECLARE @now DateTime = GETDATE()

Begin Transaction

	if (select COUNT(*) from RoleMapping) > 0
		truncate table RoleMapping
		
	if (select COUNT(*) from AuthZUserRoleMapping) > 0
		truncate table AuthZUserRoleMapping

	if (select COUNT(*) from Role) > 0
		delete from Role 
		
	if (select COUNT(*) from AuthorizationGroup) > 0
		delete from AuthorizationGroup
		
	/* Insert the Security Groups into the AuthorizationGroup table */
	Insert into AuthorizationGroup Values ('<Group for SysAdmin>', @user, @now)
	Insert into AuthorizationGroup Values ('<Group for Risk Manager>', @user, @now)
	Insert into AuthorizationGroup Values ('<Group for Task Admin>', @user, @now)
	Insert into AuthorizationGroup Values ('<Group for Report User>', @user, @now)
	Insert into AuthorizationGroup Values ('<Group for Risk User>', @user, @now)
	Insert into AuthorizationGroup Values ('<Group for Task Manager>', @user, @now)
	Insert into AuthorizationGroup Values ('<Group for Trusted Task User>', @user, @now)
	Insert into AuthorizationGroup Values ('<Group for Task User>', @user, @now)

	/* Insert the Roles into the Role table.  Set up the parent/child role hierarchy. */
	Insert into Role Values ('SysAdmin', null, @user, @now, null, null)
	Insert Role select 'Risk Manager', RoleID, @user, @now, null, null from Role where RoleName = 'SysAdmin'
	Insert Role select 'Task Admin', RoleID, @user, @now, null, null from Role where RoleName = 'SysAdmin'
	Insert Role select 'Report User', RoleID, @user, @now, null, null from Role where RoleName = 'SysAdmin'
	Insert Role select 'Risk User', RoleID, @user, @now, null, null from Role where RoleName = 'Risk Manager'
	Insert Role select 'Task Manager', RoleID, @user, @now, null, null from Role where RoleName = 'Risk Manager'
	Insert Role select 'Trusted Task User', RoleID, @user, @now, null, null from Role where RoleName = 'Task Manager'
	Insert Role select 'Task User', RoleID, @user, @now, null, null from Role where RoleName = 'Trusted Task User'
	
	declare @RoleID int;
	declare @GroupID int;

	/* Map the Security Groups to the Roles. */
	select @RoleId = RoleID from Role (nolock) where RoleName = 'SysAdmin'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for SysAdmin>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)

	select @RoleId = RoleID from Role (nolock) where RoleName = 'Risk Manager'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for Risk Manager>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)

	select @RoleId = RoleID from Role (nolock) where RoleName = 'Task Admin'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for Task Admin>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)

	select @RoleId = RoleID from Role (nolock) where RoleName = 'Report User'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for Report User>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)

	select @RoleId = RoleID from Role (nolock) where RoleName = 'Risk User'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for Risk User>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)

	select @RoleId = RoleID from Role (nolock) where RoleName = 'Task Manager'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for Task Manager>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)

	select @RoleId = RoleID from Role (nolock) where RoleName = 'Trusted Task User'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for Trusted Task User>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)
	
	select @RoleId = RoleID from Role (nolock) where RoleName = 'Task User'
	select @GroupID = GroupID from AuthorizationGroup (nolock) where GroupName = '<Group for Task User>'
	Insert RoleMapping Values (@GroupID, @RoleID, @user, @now)

Commit Transaction
